End-to-end Excel workbooks demonstrating a complete data analyst toolkit: scraping & cleaning raw data, normalizing with lookup functions, summarizing with pivot tables, modeling with regression & what-if analysis, and automating repetitive tasks with VBA macros.
Project 01 — Compensation Analytics
Excel Workbook · 9 Sheets · Multi-Company Salary Data
This workbook analyzes 979 rows of median salary data scraped from Indeed, spanning 98 US companies and 29 job categories. The goal was to demonstrate a realistic, end-to-end Excel analysis workflow: clean the raw data, normalize pay periods with lookup functions, summarize with pivot tables, run sensitivity and goal-seek analysis on pay raises, and automate reporting with VBA.
Salaries were originally reported on inconsistent bases — hourly, weekly, monthly, or yearly — so a core piece of the project was building a reliable Annual Salary figure that makes every role comparable.
The original, untouched CSV of 979 salary listings loaded as a native Excel Table (RawDataTable).
Reference table mapping HOURLY / WEEKLY / MONTHLY / YEARLY pay periods to an annual multiplier.
Raw data plus an AnnualSalary column normalized via INDEX/MATCH, and a composite lookup Key.
Live INDEX/MATCH, VLOOKUP, and HLOOKUP exercises — pick a company/job from dropdowns and watch results update.
A formula-built (SUMIFS/AVERAGEIFS/COUNTIFS) pivot by Category and Company, plus steps for a native PivotTable.
Raise-percentage sensitivity table, a Goal Seek setup, and a Scenario Manager comparing raise scenarios.
KPI cards, a company-selector dropdown, and charts summarizing salaries by category and company.
Full VBA source for three macros: clean & convert data, auto-generate a summary report, and refresh the dashboard.
Data Insights
Arts & Entertainment tops the list at an average of $163,041/year, ahead of IT Operations & Helpdesk ($159,715) and Project Management ($154,863).
Adobe leads company averages at $213,921/year, followed closely by Airbnb ($197,384) and Salesforce ($195,886).
Annual salaries range from around $15,000 in entry-level Management roles up to $423,415 in top Sales positions — a reminder to always check the full range, not just the average.
Retail, Sales, and Management are the most heavily reported categories, together accounting for thousands of individual salary reports in the dataset.
Under the Hood
The formulas, tools, and automation demonstrated across the workbook.
Exact-match lookups on a combined Company + Job Title key to pull Category and normalize AnnualSalary.
First-row match on Company to retrieve the corresponding Job Title and Category from Raw_Data.
Horizontal lookup on pay period against the Multiplier_Lookup table to fetch the annual multiplier.
SUMIFS, AVERAGEIFS, and COUNTIFS used to build a manual pivot summary by Category and Company.
Step-by-step setup for a refreshable native Excel PivotTable built directly off RawDataTable.
A raise-percentage vs. new-annual-salary table modeling outcomes from 0% to 20% raises.
Solves for the exact raise percentage required to hit a specific target annual salary.
Conservative (2%), Moderate (5%), and Aggressive (10%) raise scenarios saved for quick comparison.
Structured Tables and data-validation dropdowns power the interactive company/job lookup exercises.
Macro that automates cleaning the raw import and converting every salary to an annual basis.
Macro that auto-builds a summary report of the cleaned dataset with a single click.
Macro that rebuilds the KPI cards and charts on the Dashboard sheet from the latest data.
Project 02 — Consumer Price Analytics
Excel Workbook · 6 Sheets · BLS Average Price Data
This workbook tracks 7,814 monthly price observations for 74 grocery and energy items from the US Bureau of Labor Statistics Average Price Data series, spanning January 2015 through July 2026. The goal was to go beyond a single lookup table and build a small analytics pipeline: a long-format source table, a per-item summary with coverage and trend metrics, a wide-pivoted view for direct item-to-item comparison, an interactive dashboard, and a from-scratch regression model.
Because BLS discontinues and introduces series over time, a key part of the project was tracking is_current status so trend comparisons only run on items with a full, comparable history.
Orientation sheet explaining each tab and crediting the US BLS Average Price Data source.
The full long-format source: 7,814 rows of date, item, category, unit, and price loaded as an Excel Table (tblMonthly).
One row per item (tblItemSummary): coverage window, min/max price, 12-month average % change, and is_current flag.
Eight key items (eggs, gas, milk, beef, electricity, coffee, bread, bananas) pivoted wide — the input for the regression model.
An item-name INDEX/MATCH lookup plus a 3-column composite-key (item + year + month) array-match price lookup.
Regression of gasoline vs milk price (SLOPE/INTERCEPT/RSQ), a live what-if lever, 8-point sensitivity table, and Goal Seek setup.
Data Insights
Ground roast coffee rose 96.29% on a trailing 12-month average basis — the largest increase of any tracked item, ahead of frozen orange juice concentrate (+77.93%) and piped utility gas (+77.35%).
Spaghetti and macaroni moved just +0.15% over the same window — effectively flat across more than a decade of data, in sharp contrast to volatile categories like Beef and Energy.
14 of the 74 tracked items (19%) — including Broccoli and Frankfurters — were discontinued by BLS mid-series, which is exactly why the is_current flag matters before trusting a trend.
A simple linear regression finds gasoline prices explain ~59% of the variation in milk price (R² = 0.589): every $1/gal rise in gas predicts roughly a $0.50/gal rise in milk.
Under the Hood
The formulas, models, and automation demonstrated across the workbook.
Looks up category, unit, first/last price, min/max, and % change from ItemSummary by item name.
An array formula matching item, year, and month simultaneously — (A=x)*(B=y)*(C=z) — to pull one exact monthly price.
tblMonthly, tblItemSummary, and tblWide keep every formula and lookup range self-expanding and readable.
Builds a linear regression of gasoline price against milk price directly from the WideSelected data, no add-ins required.
Projects predicted milk price across eight gasoline-price scenarios from $2.00 to $5.50 per gallon.
Set up to solve for the exact gasoline price that would predict a target $4.50 milk price.
Editing a single hypothetical gasoline price cell instantly recalculates the predicted milk price.
First/last date, min/max price, and 12-month average % change computed per item to flag discontinued series.
Scaffolded macro to prompt for a category, roll up item counts and average % change, and export it to a new sheet.
Scaffolded macro to color-code every item row by its 12-month % change — red, amber, or green.
Scaffolded macro to refresh every PivotTable in the workbook and force a full recalculation in one click.
Let's collaborate on your next data project!
I am open to data analyst roles, Excel/BI projects, and analytical collaborations.